﻿/*========================== 
 * @author 郑金泉
 * @desc Excel表格文档类
 * ========================= */
using System;
using System.Collections.Generic;
using System.Text;

using NPOI;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

namespace Obsidian.Data.Excel
{
    public class Sheet
    {

        private Dictionary<string, int> dictColumns;

        private NPOI.SS.UserModel.Sheet npoiSheet;

        public Sheet(NPOI.SS.UserModel.Sheet npoiSheet)
        {
            this.npoiSheet = npoiSheet;
        }

        public bool TryGetInt(int rowNum, int cellNum, out int result)
        {
            string str = this.GetString(rowNum, cellNum);
            return int.TryParse(str, out result);
        }

        public Dictionary<string, int> GetColumnNums()
        {
            if (dictColumns == null)
            {
                dictColumns = new Dictionary<string, int>();
                Row row = this.npoiSheet.GetRow(0);
                if (row == null)
                    throw new Exception("首行为空");
                int lastCellNum = row.LastCellNum;
                for (int i = 0; i < lastCellNum; i++)
                {
                    Cell cell = row.GetCell(i);
                    if (cell == null)
                        throw new Exception("列头不能为空：" + i);
                    string name = this.GetCellString(cell);
                    if (dictColumns.ContainsKey(name))
                        throw new Exception("存在重复列名称:" + name);
                    dictColumns.Add(name, i);
                }
            }
            return dictColumns;
        }

        public int GetColumnNum(string columnName)
        {
            Dictionary<string, int> dictColumns = GetColumnNums();
            if (dictColumns.ContainsKey(columnName))
                return dictColumns[columnName];
            else
                return -1;
        }

        public bool HasColumn(string colName)
        {
            int colNum = this.GetColumnNum(colName);
            return colNum >= 0;
        }


        public string GetString(int rowNum, string colName)
        {
            int colNum = this.GetColumnNum(colName);
            if (colNum < 0)
                throw new Exception("列 " + colName + " 不存在");
            return GetString(rowNum, colNum);
        }

        public string GetString(int rowNum, int cellNum)
        {
            Row row = this.npoiSheet.GetRow(rowNum);
            if (row == null)
                return null;
            Cell cell = row.GetCell(cellNum);
            if (cell == null)
                return null;
            return this.GetCellString(cell);
        }

        public object GetValue(int rowNum, int cellNum)
        {
            Row row = this.npoiSheet.GetRow(rowNum);
            if (row == null)
                return null;
            Cell cell = row.GetCell(cellNum);
            if (cell == null)
                return null;
            return this.GetCellValue(cell);
        }

        public int LastCellNum(int rowNum)
        {
            Row row = this.npoiSheet.GetRow(rowNum);
            if (row == null)
                return -1;
            else
            {
                return row.LastCellNum;
            }
        }

        public int LastRowNum
        {
            get
            {
                return this.npoiSheet.LastRowNum;
            }
        }

        public string Name
        {
            get
            {
                return this.npoiSheet.SheetName;
            }
        }

        public void SetValue(int rowNum, int cellNum, string val)
        {
            Cell cell = GetOrCreateCell(rowNum, cellNum);
            cell.SetCellValue(val);
        }

        public void SetValue(int rowNum, int cellNum, double val)
        {
            Cell cell = GetOrCreateCell(rowNum, cellNum);
            cell.SetCellValue(val);
        }

        public void SetValue(int rowNum, string colName, string val)
        {
            int colNum = this.GetColumnNum(colName);
            this.SetValue(rowNum, colNum, val);
        }

        public void SetValue(int rowNum, string colName, double val)
        {
            int colNum = this.GetColumnNum(colName);
            this.SetValue(rowNum, colNum, val);
        }

        public bool TrySetValue(int rowNum, string colName, string val)
        {
            int colNum = this.GetColumnNum(colName);
            if (colNum >= 0)
            {
                this.SetValue(rowNum, colNum, val);
                return true;
            }
            else
            {
                return false;
            }
        }

        private Cell GetOrCreateCell(int rowNum, int cellNum)
        {
            Row row = this.npoiSheet.GetRow(rowNum);
            if (row == null)
                row = this.npoiSheet.CreateRow(rowNum);
            Cell cell = row.GetCell(cellNum);
            if (cell == null)
                cell = row.CreateCell(cellNum);
            return cell;
        }

        private string GetCellString(Cell cell)
        {
            switch (cell.CellType)
            {
                case CellType.BLANK:
                    return "";
                case CellType.BOOLEAN:
                    return cell.BooleanCellValue.ToString();
                case CellType.ERROR:
                    return cell.ErrorCellValue.ToString();
                case CellType.NUMERIC:
                    return cell.NumericCellValue.ToString();
                case CellType.FORMULA:
                    try
                    {
                        return cell.NumericCellValue.ToString();
                    }
                    catch
                    {
                        try
                        {
                            return cell.DateCellValue.ToString("yyyy-MM-dd hh:mm:ss");
                        }
                        catch
                        {
                            try
                            {
                                return cell.RichStringCellValue.ToString();
                            }
                            catch
                            {
                                return cell.StringCellValue.ToString();
                            }
                        }
                    }
                default:
                    return cell.StringCellValue.ToString();
            }
        }

        private object GetCellValue(Cell cell)
        {
            switch (cell.CellType)
            {
                case CellType.BLANK:
                    return "";
                case CellType.BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.ERROR:
                    return cell.ErrorCellValue;
                case CellType.NUMERIC:
                    return cell.NumericCellValue;
                case CellType.FORMULA:
                    return cell.CellFormula;
                default:
                    return cell.StringCellValue;
            }
        }

    }
}
